package com.aaa.project.zhreport.service.impl;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.StrUtil;
import com.aaa.common.exception.ServiceException;
import com.aaa.common.utils.AESUtil;
import com.aaa.common.utils.SecurityUtils;
import com.aaa.common.utils.bean.BeanUtils;
import com.aaa.common.utils.http.HttpUtils;
import com.aaa.framework.security.LoginUser;
import com.aaa.project.file.service.ISysFileService;
import com.aaa.project.zhreport.controller.dto.*;
import com.aaa.project.zhreport.domain.*;
import com.aaa.project.zhreport.enums.ApplyStatusEnum;
import com.aaa.project.zhreport.mapper.*;
import com.aaa.project.zhreport.service.DataFetchService;
import com.aaa.project.zhreport.service.ExcelParseService;
import com.aaa.project.zhreport.service.YwfxDataApplyService;
import com.alibaba.fastjson2.JSONObject;
import lombok.extern.slf4j.Slf4j;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.nio.charset.StandardCharsets;
import java.util.*;

/**
 * @author baibin
 * @version 1.0
 * @date 2025/4/9 10:14
 * @description YwfxDataApplyServiceImpl
 */
@Service
@Slf4j
public class YwfxDataApplyServiceImpl implements YwfxDataApplyService {

    private static final Logger logger = LoggerFactory.getLogger(YwfxDataApplyServiceImpl.class);

    @Resource
    private YwfxDataApplyMapper ywfxDataApplyMapper;
    @Resource
    private YwfxDataApplyUserMapper ywfxDataApplyUserMapper;
    @Resource
    private YwfxFileDownloadRecordMapper ywfxFileDownloadRecordMapper;
    @Resource
    private DataFetchService dataFetchService;
    @Resource
    private YwfxDataTemplateConfigMapper ywfxDataTemplateConfigMapper;
    @Resource
    private YwfxDataSourcesMapper ywfxDataSourcesMapper;
    @Resource
    private ExcelParseService excelParseService;
    @Resource
    private ISysFileService sysFileService;
    @Value("${order.url}")
    private String url;
    @Value("${order.processId}")
    private String processId;
    @Value("${order.clientId}")
    private String clientId;
    @Value("${order.password}")
    private String password;

    @Transactional
    @Override
    public void apply(YwfxDataApplyReqDTO reqDTO, HttpServletRequest httpRequest) {
        logger.info("apply reqDTO:{}", reqDTO);
        // 获取当前登录人
        LoginUser loginUser = SecurityUtils.getLoginUser();
        reqDTO.setCreateBy(loginUser.getUser().getUserName());
        reqDTO.setUpdateBy(loginUser.getUser().getUserName());
        // 校验模板是否存在
        YwfxDataTemplateConfig templateConfig = getTemplateConfig(reqDTO);
        // 查询数据源
        YwfxDataSources dataSources = getDataSource(templateConfig);

        YwfxDataApply ywfxDataApply = new YwfxDataApply();
        BeanUtils.copyBeanProp(ywfxDataApply, reqDTO);
        // 设置审批状态为待审批-
        ywfxDataApply.setStatus(ApplyStatusEnum.APPROVING.getCode());
        ywfxDataApply.setSql(reqDTO.getPreviewSql());
        ywfxDataApplyMapper.insertYwfxDataApply(ywfxDataApply);
        // 上传临时表数据
        // 修改了附件 需要进行上传
        String url = null;
        if (reqDTO.getAttachment() != null) {
            checkFileSize(reqDTO.getAttachment().getSize());
            templateConfig.setAttachmentName(reqDTO.getAttachment().getOriginalFilename());
            // 解析EXCEL文件，需要判断excel内容是否为空，根据入参的临时表名、以及解析excel表头作为临时表字段，
            // 然后根据dataSources的数据库类型字段dataSourceType(PGSQL/ORACLE)生成DDL语句，并执行，内容生成临时表数据
            try {
                // 读取 Excel 文件内容
                List<List<Object>> rows = readTxtFromStream(reqDTO.getAttachment().getInputStream());
                // 判断excel数据是否为空
                if (CollUtil.isEmpty(rows) || rows.size() <= 1) {
                    throw new ServiceException("上传的临时表Excel文件内容不能为空！");
                }
                excelParseService.parseExcelAndGenerateTable(rows, templateConfig.getTempTableName(), dataSources);
            } catch (ServiceException e) {
                throw new ServiceException(e.getMessage());
            } catch (Exception e) {
                log.error("解析临时表Excel文件失败！", e);
                throw new ServiceException("解析临时表Excel文件失败！");
            }

            try {
                String fileName = convertFileName(reqDTO.getAttachment().getOriginalFilename());
                url = sysFileService.uploadFile(reqDTO.getAttachment().getInputStream(), fileName);
                templateConfig.setAttachmentPath(url);
            } catch (Exception e) {
                log.error("上传附件失败！", e);
                throw new ServiceException(e.getMessage());
            }
        }
        // 修改临时表文件数据
        ywfxDataTemplateConfigMapper.update(templateConfig);

        // 新增审批人员列表数据
        List<YwfxDataApplyUser> userList = new ArrayList<>();
        reqDTO.getUserIds().forEach(userId -> {
            YwfxDataApplyUser ywfxDataApplyUser = new YwfxDataApplyUser();
            ywfxDataApplyUser.setApplyId(ywfxDataApply.getId());
            ywfxDataApplyUser.setUserId(userId);
            ywfxDataApplyUser.setCreateBy(reqDTO.getCreateBy());
            ywfxDataApplyUser.setUpdateBy(reqDTO.getUpdateBy());
            userList.add(ywfxDataApplyUser);
        });
        ywfxDataApplyUserMapper.batchInsert(userList);
        logger.info("apply success id:{}", ywfxDataApply.getId());
    }

    private YwfxDataSources getDataSource(YwfxDataTemplateConfig templateConfig) {
        YwfxDataSources da = ywfxDataSourcesMapper.findById(templateConfig.getDataSourceId());
        if (da == null) {
            throw new ServiceException("数据源不存在！");
        }
        return da;
    }

    private YwfxDataTemplateConfig getTemplateConfig(YwfxDataApplyReqDTO reqDTO) {
        YwfxDataTemplateConfig templateConfig = ywfxDataTemplateConfigMapper.findById(reqDTO.getTemplateId());
        if (templateConfig == null) {
            throw new ServiceException("模板不存在！");
        }
        return templateConfig;
    }

    @Override
    public List<YwfxDataApplyResDTO> list(YwfxDataApplyQueryDTO request) {
        logger.info("list request:{}", request);
        // 获取当前登录人
        LoginUser loginUser = SecurityUtils.getLoginUser();
        // 如果是管理员就跳过userid的条件筛选，展示全部
        Long deptId = loginUser.getUser().getDeptId();
        if (deptId != 100) {
            request.setCreateBy(loginUser.getUser().getUserName());
            request.setUserId(loginUser.getUser().getUserId());
        }
        List<YwfxDataApplyResDTO> ywfxDataApplyList = ywfxDataApplyMapper.selectYwfxDataApplyList(request);

        List<ApplyUserResDTO> list = ywfxDataApplyMapper.findManage(100l);
        // 循环设置users参数，添加管理员审批权限
        for (YwfxDataApplyResDTO ywfxDataApplyResDTO : ywfxDataApplyList) {
            List<ApplyUserResDTO> users = new ArrayList<>();
            users.addAll(ywfxDataApplyResDTO.getUsers());
            users.addAll(list);
            ywfxDataApplyResDTO.setUsers(users);

        }
        return ywfxDataApplyList;
    }

    @Override
    public String approve(YwfxDataApplyProcessReqDTO request) {
        logger.info("approve request:{}", request);
        // 获取当前登录人
        LoginUser loginUser = SecurityUtils.getLoginUser();
        // 校验当前审批人是否是审批人员列表中的成员
        YwfxDataApplyUser applyUser = ywfxDataApplyUserMapper.selectByApplyIdAndUserId(request.getId(), loginUser.getUser().getUserId());
        if (applyUser == null && loginUser.getDeptId() != 100) {
            throw new ServiceException("当前审批人没有权限审批！");
        }
        YwfxDataApply vo = ywfxDataApplyMapper.findById(request.getId());
        if (vo == null) {
            throw new ServiceException("数据不存在！");
        }
        // 更新审批状态
        BeanUtils.copyBeanProp(vo, request);
        vo.setUpdateBy(loginUser.getUser().getUserName());
        vo.setApprovalTime(new Date());
        //如果是审批通过，则生成批次号，并更新审批状态
        String batchNumber = null;
        if (ApplyStatusEnum.APPROVED.getCode().equals(request.getStatus())) {
            // 生成批次号 前缀 BTACH_ + 当前时间戳 + 随机数
            batchNumber = StrUtil.format("BTACH_{}_{}", vo.getOrderNumber(), vo.getId());
        }
        vo.setBatchNumber(batchNumber);
        ywfxDataApplyMapper.updateYwfxDataApply(vo);
        // 审批通过进行异步数据处理
        if (StrUtil.isNotBlank(batchNumber)) {
            // 生成文件下载记录数据
            YwfxFileDownloadRecord ywfxFileDownloadRecord = new YwfxFileDownloadRecord(vo.getCreateBy(), request.getId(),
                    batchNumber);
            ywfxFileDownloadRecordMapper.insert(ywfxFileDownloadRecord);
            // 异步进行提取数据
            dataFetchService.asyncFetchDataByTemplateId(vo.getTemplateId(), ywfxFileDownloadRecord.getId(), batchNumber, vo.getSql());
        }
        logger.info("approve success id:{} batchNumber:{}", request.getId(), batchNumber);
        return batchNumber;
    }

    @Override
    public YwfxDataApplyDetailResDTO detail(Long id) {
        logger.info("detail id:{}", id);
        YwfxDataApplyQueryDTO query =  new YwfxDataApplyQueryDTO();
        query.setId(id);
        List<YwfxDataApplyResDTO> resList = ywfxDataApplyMapper.selectYwfxDataApplyList(query);
        // 判断集合是否为空
        if (CollUtil.isEmpty(resList)) {
            throw new ServiceException("详情数据不存在！");
        }
        YwfxDataApplyDetailResDTO res = new YwfxDataApplyDetailResDTO();
        BeanUtils.copyBeanProp(res, resList.get(0));
        logger.info("detail id:{} res:{}", id, res);
        return res;
    }

    @Override
    public String generateSql(YwfxDataApplyReqDTO reqDTO) {
        logger.info("generateSql reqDTO:{}", reqDTO);
        // 校验模板是否存在
        YwfxDataTemplateConfig templateConfig = getTemplateConfig(reqDTO);
        // 查询数据源
        YwfxDataSources da = getDataSource(templateConfig);
        String sql = reqDTO.convertSQL(templateConfig.getExecSql(), da.getDataSourceType());
        logger.info("generateSql sql:{}", sql);
        return sql;
    }

    @Override
    public void preExecuteSql(YwfxDataApplyReqDTO reqDTO) {
        logger.info("preExecuteSql reqDTO:{}", reqDTO);
        // 校验模板是否存在
        YwfxDataTemplateConfig templateConfig = getTemplateConfig(reqDTO);
        // 查询数据源
        YwfxDataSources da = getDataSource(templateConfig);
        // 预执行sql 条数改1
        String previewSql = reqDTO.getSqlLimit(reqDTO.getPreviewSql(), 1L, da.getDataSourceType());
        dataFetchService.preExecuteSql(previewSql, da);
    }

    @Override
    public String queryOrderList(String userId, String orderTime) {
        logger.info("queryOrderList userId:{},orderTime:{}", userId, orderTime);
        try {
            JSONObject param = new JSONObject();
            Random random = new Random();
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < 24; i++) {
                sb.append(random.nextInt(10)); // 0-9的随机数字
            }
            param.put("applyId","SJZTSJJ202502050007493047392700");
            param.put("processId",processId);
            param.put("clientId",clientId);
            param.put("password",password);
            param.put("queryTime",orderTime);
            param.put("loginId",userId);
            String encryptedJson = AESUtil.encrypt(param.toString());
            String result = HttpUtils.sendPost(url, encryptedJson);
            return result;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 判断文件大小
     *
     * @param size
     */
    private void checkFileSize(long size) {
        // 校验文件大小（限制为 5MB）
        long maxFileSize = 5 * 1024 * 1024; // 5MB
        if (size > maxFileSize) {
            throw new ServiceException("上传的文件大小不能超过 5MB！");
        }
    }

    private List<List<Object>> readTxtFromStream(InputStream inputStream) {
        List<List<Object>> rows = new ArrayList<>();
        try (BufferedReader reader = new BufferedReader(new InputStreamReader(inputStream, StandardCharsets.UTF_8))) {
            String line;
            while ((line = reader.readLine()) != null) {
                // 按逗号分割每一行
                String[] cells = line.split(",", -1);
                List<Object> row = new ArrayList<>();
                for (String cell : cells) {
                    row.add(cell.trim());
                }
                rows.add(row);
            }
        } catch (Exception e) {
            log.error("读取 TXT 文件失败！", e);
            throw new ServiceException("读取 TXT 文件失败！");
        }
        return rows;
    }

    /**
     * 转换文件名
     *
     * @param originalFilename
     * @return
     */
    private String convertFileName(String originalFilename) {
        String fileMainName = FileUtil.mainName(originalFilename);
        String fileExtension = FileUtil.extName(originalFilename);
        String fileName = StrUtil.format("{}_{}.{}", fileMainName, System.currentTimeMillis(), fileExtension);
        return fileName;
    }
}
